SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE [dbo].[BackTestingRollup] 
 @FundId   VARCHAR(MAX ),

@StartDate DATETIME ,
@EndDate DATETIME  	

AS

BEGIN
	SET FMTONLY ON;
    DECLARE @fmtonlyON BIT
    SET @fmtonlyON = 0
    IF ( 1 = 0 ) 
        BEGIN
            SET @fmtonlyON = 1
        END
    SET FMTONLY OFF;

	
    

DECLARE @ExportId INT  
-- SET @FundId   = 'FSIC'
 SET @ExportId =
 (SELECT MAX(e.ExportId)
 FROM    FSFundModel.Reporting.Export e
 WHERE   e.FundId = @FundId
 )
 SELECT    
			DISTINCT t.tradeid, 
			 i.ExtId
			, cusipmap.ssbGroupedCusip
          , i.InstrName
		  , i.PricingVendor
          , CASE WHEN a.EndAdjAmt > 0 THEN 'Partial Sale'
		  ELSE 'Full Sale'
		  END AS SaleType
		  ,  FNTodayPrice.volume TodayDepth	

		  , a.EndAdjAmt AS AmtLeftInPortfolio
		  , t.Proceeds
          , t.TradeDate
		  , t.TradePrice
	
	   ,FNTodayPrice.bid  todayB	
	   , FNTodayPrice.mid todayM
	   , FNTodayPrice.ask todayA
	
	
	
		   , yesterdayPriceFN.bid  yesterdayb
	   , yesterdayPriceFN.Mid yesterdayM
	   , yesterdayPriceFN.ask  yesterdayA

    
		 ,(1- (t.tradeprice/ yesterdayPriceFN.mid ))*100 AS YesterdayPctDeviation
		 ,(1- (t.tradeprice/ FNTodayPrice.mid ))*100 AS TodayPctDeviation
		 , CASE WHEN ABS((1- (t.tradeprice/ yesterdayPriceFN.mid))*100) > 2 THEN 
			'Investigate' 
			ELSE '' 
			END AS '2% Test Yesterday'
		 , CASE WHEN ABS((1- (t.tradeprice/ yesterdayPriceFN.mid))*100) > 3 THEN 
			'Investigate' 
			ELSE '' 
			END AS '3% Test Yesterday'
		  , CASE WHEN ABS((1- (t.tradeprice/ yesterdayPriceFN.mid))*100) > 5 THEN 
			'Investigate' 
			ELSE '' 
			END AS '5% Test Yesterday'
				 , CASE WHEN ABS((1- (t.tradeprice/ FNTodayPrice.mid))*100) > 2 THEN 
			'Investigate' 
			ELSE '' 
			END AS '2% Test Today'
		 , CASE WHEN ABS((1- (t.tradeprice/ FNTodayPrice.mid))*100) > 3 THEN 
			'Investigate' 
			ELSE '' 
			END AS '3% Test Today'
		  , CASE WHEN ABS((1- (t.tradeprice/FNTodayPrice.mid))*100) > 5 THEN 
			'Investigate' 
			ELSE '' 
			END AS '5% Test Today'
     --     , ReportingScripts.dbo.fnGetSpecificNAVDatePrice(@ExportId, f.PrevNAVDate,i.ExtId, 'Depth') priorDepth
		
 FROM       FSFundModel.Reporting.Trade t
            
            INNER JOIN FSFundModel.Reporting.Asset a
            ON         a.ExportId     = t.ExportId
            AND        t.InstrumentId = a.InstrumentId
            AND        t.LotId        = a.LotId
			AND t.PortfolioId = a.PortfolioId
            
            INNER JOIN FSFundModel.Reporting.Instrument i
            ON         i.ExportId     = t.ExportId
            AND        t.InstrumentId = i.InstrumentId
            
            
            INNER JOIN FSFundModel.Reporting.InstrumentDefinition id
            ON         id.ExportId = t.ExportId
            AND        i.InstDefId = id.InstDefId
            
            
            INNER JOIN FSFundModel.Reporting.Issuer iss
            ON         iss.ExportId = t.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel.Reporting.Portfolio p
            ON         t.PortfolioId = p.PortfolioId
            AND        p.ExportId    = t.ExportId
            
            INNER JOIN FSFundModel.Reporting.Fund f
            ON         f.ExportId = t.ExportId
            AND        f.FundId   = @FundId
          
			LEFT  JOIN ReportingScripts.dbo.Duplicate_SSB_CUSIPS cusipMap ON 
			cusipMap.FS_ID = i.ExtId 
        CROSS APPLY (SELECT  
		 yPrc.LXID ,
		        yPrc.NAVDate ,
		        yPrc.Bid ,
		        yPrc.Mid ,
		        yPrc.Ask ,
		        yPrc.Volume FROM
  ReportingScripts.dbo.tvf_Generic_GetSpecificNAVDatePriceTBL(t.TradeDate, -1)  AS yPrc
  WHERE yPrc.LXID = i.ExtId 

  
  )  YesterdayPriceFN 



  CROSS		APPLY (SELECT  tPrc.LXID ,
          tPrc.NAVDate ,
          tPrc.Bid ,
          tPrc.Mid ,
          tPrc.Ask ,
          tPrc.Volume FROM  ReportingScripts.dbo.tvf_Generic_GetSpecificNAVDatePriceTBL(t.TradeDate, 0)  AS tPrc
  WHERE tPrc.LXID = i.ExtId  
  
  
  ) FNTodayPrice
        
 
 WHERE      t.ExportId         = @ExportId
 --AND YesterdayPriceFN.LXID = i.ExtId 
 
 
 --AND        a.IsActiveEndOfDay = 1
 AND        t.TradeType        = 'Sale'
 AND     t.TradeDate BETWEEN @StartDate AND     @EndDate
 ORDER BY (1- (t.tradeprice/ yesterdayPriceFN.mid ))*100 DESC ,  t.TradeDate DESC	, i.InstrName ASC
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Accounting', 'SCHEMA', N'dbo', 'PROCEDURE', N'BackTestingRollup', NULL, NULL
GO
